USE MASTER
GO

BEGIN TRY
    -- kill all other connections
    ALTER DATABASE [EasyTicket]
    SET SINGLE_USER 
    WITH ROLLBACK IMMEDIATE;

    DROP DATABASE [EasyTicket];
END TRY 
BEGIN CATCH
END CATCH
GO

CREATE DATABASE [EasyTicket]
GO

ALTER DATABASE [EasyTicket]
SET MULTI_USER 
GO

USE [EasyTicket]
GO


------------------------------------------------------
-- TABLE ----- [M001_USER_PROFILE] ------------------------
------------------------------------------------------
CREATE TABLE [M001_USER_PROFILE]
(
    [UserId] INT IDENTITY(1000,1) NOT NULL,
    [UserName] NVARCHAR(100) NOT NULL,
    CONSTRAINT _PK_UserProfile PRIMARY KEY ([UserId]),
    CONSTRAINT _UC_UserProfile_UserName UNIQUE ([UserName])
)
GO


------------------------------------------------------
-- TABLE ----- [M051_WEB_MEMBERSHIP] ----------------------
------------------------------------------------------
CREATE TABLE [M051_WEB_MEMBERSHIP]
(
    [UserId] INT NOT NULL,
    [CreateDate] DATETIME NOT NULL,
    [ConfirmationToken] nvarchar(128) NULL,
    [IsConfirmed] BIT NULL,
	[IsActived] BIT NULL,
	[IsDeleted] BIT NULL,
    [LastPasswordFailureDate] DATETIME NULL,
    [PasswordFailuresSinceLastSuccess] INT NOT NULL,
    [Password] NVARCHAR(128) NOT NULL,
    [PasswordChangedDate] DATETIME NULL,
    [PasswordSalt] NVARCHAR(128) NOT NULL,
    [PasswordVerificationToken] NVARCHAR(128) NULL,
    [PasswordVerificationTokenExpirationDate] DATETIME NULL,
    CONSTRAINT _PK_WebMembership PRIMARY KEY ([UserId])
)
GO

ALTER TABLE [M051_WEB_MEMBERSHIP]
ADD DEFAULT GETDATE() FOR [CreateDate]
GO

ALTER TABLE [M051_WEB_MEMBERSHIP]
ADD DEFAULT 0 FOR [IsConfirmed]
GO

ALTER TABLE [M051_WEB_MEMBERSHIP] 
ADD  DEFAULT 0 FOR [PasswordFailuresSinceLastSuccess]
GO

ALTER TABLE [M051_WEB_MEMBERSHIP] 
ADD  DEFAULT 1 FOR [IsActived]
GO

ALTER TABLE [M051_WEB_MEMBERSHIP] 
ADD  DEFAULT 0 FOR [IsDeleted]
GO

------------------------------------------------------
-- TABLE ----- [M061_WEB_ROLES] ---------------------------
------------------------------------------------------
CREATE TABLE [M061_WEB_ROLES]
(
    [RoleId] INT IDENTITY(1000,1) NOT NULL,
    [RoleName] NVARCHAR(256) NOT NULL,
    [RoleDescription] NVARCHAR(MAX) NULL,
    CONSTRAINT _PK_WebRoles PRIMARY KEY ([RoleId]),
    CONSTRAINT _UC_WebRoles_RoleName UNIQUE ([RoleName])
)
GO


------------------------------------------------------
-- TABLE ----- [M062_WEB_USER_IN_ROLES] -------------------
------------------------------------------------------
CREATE TABLE [M062_WEB_USER_IN_ROLES]
(
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    CONSTRAINT _PK_WebUserInRoles PRIMARY KEY ([UserId], [RoleId])
)
GO

------------------------------------------------------
-- TABLE ----- [C001_COUNTRY] -----------------------------
------------------------------------------------------
CREATE TABLE [C001_COUNTRY]
(
	[CountryId] INT IDENTITY(1000,1) NOT NULL,
	[CountryName] NVARCHAR(100) NOT NULL,
	CONSTRAINT _PK_Country PRIMARY KEY ([CountryId]),
	CONSTRAINT _UC_Country_CountryName UNIQUE ([CountryName])
)
GO


------------------------------------------------------
-- TABLE ----- [C002_CITY] --------------------------------
------------------------------------------------------
CREATE TABLE [C002_CITY]
(
	[CityId] INT IDENTITY(1000,1) NOT NULL,
	[CountryId] INT NOT NULL,
	[CityName] NVARCHAR(100) NOT NULL,
	CONSTRAINT _PK_City PRIMARY KEY ([CityId]),
	CONSTRAINT _UC_City_CityName UNIQUE ([CountryId], [CityName])
)
GO


------------------------------------------------------
-- TABLE ----- [E001_EVENT_CATEGORY] ----------------------
------------------------------------------------------
CREATE TABLE [E001_EVENT_CATEGORY]
(
	[CategoryId] INT IDENTITY(1000,1) NOT NULL,
	[CategoryName] NVARCHAR(100) NOT NULL,
	[CategoryDescription] NVARCHAR(MAX) NULL,
	CONSTRAINT _PK_EventCategory PRIMARY KEY ([CategoryId]),
    CONSTRAINT _UC_EventCategory_CategoryName UNIQUE ([CategoryName])
)
GO

------------------------------------------------------
-- TABLE ----- [E002_EVENT] -------------------------------
------------------------------------------------------
CREATE TABLE [E002_EVENT]
(
	[EventId] INT IDENTITY(1000,1) NOT NULL,
	[EventName] NVARCHAR(MAX) NOT NULL,
	[CreateDate] DATETIME NOT NULL,
	[Creator] INT NOT NULL,
	[StartDate] DATETIME NOT NULL,
	[EndDate] DATETIME NOT NULL,
	[IsCancelled] BIT NOT NULL,
	[CategoryId] INT NOT NULL,
	[EventDetails] XML NULL,
	CONSTRAINT _PL_Event PRIMARY KEY ([EventId])
)
GO

ALTER TABLE [E002_EVENT]
ADD DEFAULT GETDATE() FOR [CreateDate]
GO

ALTER TABLE [E002_EVENT]
ADD DEFAULT 0 FOR [IsCancelled]
GO


------------------------------------------------------
-- TABLE ----- [S001_SHOW] --------------------------------
------------------------------------------------------
CREATE TABLE [S001_SHOW]
(
	[ShowId] INT IDENTITY(1000,1) NOT NULL,
	[EventId] INT NOT NULL,
	[ShowName] NVARCHAR(MAX) NOT NULL,
	[CreateDate] DATETIME NOT NULL,
	[Creator] INT NOT NULL,
	[StartDate] DATETIME NOT NULL,
	[EndDate] DATETIME NOT NULL,
	[CityId] INT NOT NULL,
	[IsCancelled] BIT NOT NULL,
	[ShowDetails] XML NULL,
	CONSTRAINT _PL_Show PRIMARY KEY ([ShowId])
)
GO


------------------------------------------------------
---------------- FOREIGNT KEYS -----------------------
------------------------------------------------------
--ALTER TABLE [M051_WEB_MEMBERSHIP]
--ADD CONSTRAINT _FK_WebMemberShip_UserProfile_On_UserId 
--FOREIGN KEY ([UserId]) 
--REFERENCES [M001_USER_PROFILE]([UserId])
--GO

--ALTER TABLE [M062_WEB_USER_IN_ROLES]
--ADD CONSTRAINT _FK_WebUserInRoles_UserProfile_On_UserId 
--FOREIGN KEY ([UserId]) 
--REFERENCES [M001_USER_PROFILE]([UserId])
--GO

--ALTER TABLE [M062_WEB_USER_IN_ROLES]
--ADD CONSTRAINT _FK_WebUserInRoles_Roles_On_RoleId 
--FOREIGN KEY ([RoleId]) 
--REFERENCES [M061_WEB_ROLES]([RoleId])
--GO

--ALTER TABLE [E002_EVENT]
--ADD CONSTRAINT _FK_Event_EventCategory_On_CategoryId
--FOREIGN KEY ([CategoryId])
--REFERENCES [E001_EVENT_CATEGORY]([CategoryId])
--GO

--ALTER TABLE [E002_EVENT]
--ADD CONSTRAINT _FK_Event_UserProfile_On_UserId
--FOREIGN KEY ([Creator])
--REFERENCES [M001_USER_PROFILE]([UserId])
--GO

--ALTER TABLE [S001_SHOW]
--ADD CONSTRAINT _FK_Show_Event_On_EventId
--FOREIGN KEY ([EventId])
--REFERENCES [E002_EVENT]([EventId])
--GO

--ALTER TABLE [S001_SHOW]
--ADD CONSTRAINT _FK_Show_UserProfile_On_UserId
--FOREIGN KEY ([Creator])
--REFERENCES [M001_USER_PROFILE]([UserId])
--GO

--ALTER TABLE [C002_CITY]
--ADD CONSTRAINT _FK_City_Country_On_CountryId
--FOREIGN KEY ([CountryId])
--REFERENCES [C001_COUNTRY]([CountryId])
--GO

--ALTER TABLE [S001_SHOW]
--ADD CONSTRAINT _FK_Show_City_On_CityId
--FOREIGN KEY ([CityId])
--REFERENCES [C002_CITY]([CityId])
--GO

USE MASTER
GO